SET NOCOUNT ON;
go
/*********************************************************************************************************
*					 
*Work In Progress
*					 
*Script Developer:sfibich
*Script Name:Get_StatsDetails.sql
*Script Version:1.0.2013.09.02
*Tester:
*Tested on SQL Versions:2008R2
*Script Description:Gives the detailed statitics information.
*					 
*Work In Progress
*					 
*********************************************************************************************************/

declare @tsql varchar(max);
declare @schema_name sysname;
declare @object_name sysname;
declare @statistics_name sysname;
declare @sample_date datetime;

declare @stats_header table (
Statistics_Name sysname not null
,Updated datetime null
,Rows bigInt null
,Rows_Sampled bigInt null
,Steps smallint null
,Density numeric (9,7)
,Average_Key_Length int null
,String_Index char(3) null
,Filter_Expression varchar(max)
,Unfiltered_Rows bigInt null
);

declare @stats_histogram table(
Range_Hi_Key varchar(200) null
,Range_Rows numeric(19,2) null
,EQ_Rows numeric(19,2) null
,Distinct_Range_Rows numeric(19,2) null
,Avg_Range_Rows numeric(19,2) null
)

if (select object_id('tempdb..#stats_header')) is not null
	drop table #stats_header;
	
create table #stats_header (
Schema_Name sysname not null
,Object_Name sysname not null
,Statistics_Name sysname not null
,Updated datetime null
,Rows bigInt null
,Rows_Sampled bigInt null
,Steps smallint null
,Density numeric (9,7)
,Average_Key_Length int null
,String_Index char(3) null
,Filter_Expression varchar(max)
,Unfiltered_Rows bigInt null
,Sample_Date datetime not null
);

if (select object_id('tempdb..#stats_histogram')) is not null
	drop table #stats_histogram;
	
create table #stats_histogram (
Schema_Name sysname not null
,Object_Name sysname not null
,Statistics_Name sysname not null
,Range_Hi_Key varchar(200) null
,Range_Rows numeric(19,2) null
,EQ_Rows numeric(19,2) null
,Distinct_Range_Rows numeric(19,2) null
,Avg_Range_Rows numeric(19,2) null
,Sample_Date datetime not null
);

BEGIN TRY
select @sample_date =GETDATE();
declare looper cursor static forward_only local for
select
object_schema_name(object_id) as [schema_name]
,object_name(object_id) as [object_name]
,name as [statistics_name]
from
sys.stats s
where
object_schema_name(object_id)<>'sys'

open looper;
fetch next from looper into
@schema_name,@object_name,@statistics_name;

while @@FETCH_STATUS=0
BEGIN

	set @tsql='dbcc show_statistics ('''+@schema_name+'.'
	+@object_name+''','''+@statistics_name+''') WITH STAT_HEADER'

	insert into @stats_header
	exec(@tsql);

	set @tsql='dbcc show_statistics ('''+@schema_name+'.'
	+@object_name+''','''+@statistics_name+''') WITH HISTOGRAM'
	insert into @stats_histogram 
	exec(@tsql);
	/*The table variable needs to be cleared right away as 
	different tables with the same stat name could exists.
	This is the only way to insure that you are matching the correct
	schema and object name to the current statistic name*/
	insert into #stats_header 
	select
	@schema_name 
	,@object_name 
	,s.Statistics_Name
	,s.Updated
	,s.Rows 
	,s.Rows_Sampled
	,s.Steps
	,s.Density
	,s.Average_Key_Length
	,s.String_Index
	,s.Filter_Expression 
	,s.Unfiltered_Rows 
	,@sample_date 
	from
	@stats_header s;

	insert into #stats_histogram
	select
	@schema_name 
	,@object_name 
	,@statistics_name 
	,Range_Hi_Key
	,Range_Rows
	,EQ_Rows
	,Distinct_Range_Rows
	,Avg_Range_Rows
	,@sample_date 
	from
	@stats_histogram s;

	delete @stats_header;
	delete @stats_histogram;

	fetch next from looper into
	@schema_name,@object_name,@statistics_name;
END

close looper;
deallocate looper;

select
*
from
#stats_header;

select
*
from
#stats_histogram 

END TRY
BEGIN CATCH
	
	Print 'In Catch Block';
	
END CATCH